Course: MySQL Training
Topic: ON DELETE and ON UPDATE Referential Actions
Course Link: MySQL Training Course at JustAcademy
Demo Registration: Register for a Course Demo
JustAcademy Website: Visit JustAcademy
Official MySQL Documentation: MySQL Foreign Key Constraints and Referential Actions
MySQL provides foreign key constraints to maintain relationships between tables. When one table references another table, the relationship must remain consistent when records are deleted or referenced key values are updated.
ON DELETE and ON UPDATE are referential action clauses used with foreign key constraints. They define what MySQL should do to related records in a child table when a referenced record in a parent table is deleted or its referenced key value changes.
For example, consider an online shopping database containing a customers table and an orders table. Each order belongs to a customer. If a customer is deleted, the database needs a rule for handling that customer's orders. Depending on the business requirement, MySQL can prevent the deletion, delete related orders automatically, or set the customer reference to NULL if the column permits it.
Similarly, if a referenced key value changes in the parent table, ON UPDATE determines how MySQL handles the corresponding foreign key values in the child table.
After completing this topic, you will be able to:
Before learning referential actions, it is important to understand how foreign keys connect database tables.
A parent table contains the referenced key values. These values are typically stored in a PRIMARY KEY or an eligible indexed key.
Example:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;The departments table stores department information. The department_id column identifies each department.
A child table contains a foreign key that references the parent table. The foreign key establishes a relationship between the two tables.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INT,
CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
) ENGINE=InnoDB;The employees table references departments through department_id. If an employee belongs to department 10, the referenced department must exist in the departments table.
| departments Table | employees Table |
|---|---|
| department_id = 10 | employee_id = 1, department_id = 10 |
| department_id = 20 | employee_id = 2, department_id = 10 |
| department_id = 30 | employee_id = 3, department_id = 20 |
In this example, employees 1 and 2 belong to department 10, while employee 3 belongs to department 20. These relationships must remain consistent when parent records are updated or deleted.
A referential action is a rule that controls how a foreign key relationship responds when an operation affects a referenced key in the parent table.
MySQL supports the following main referential actions for InnoDB foreign keys:
| Action | Meaning |
|---|---|
| CASCADE | Propagates the parent deletion or key update to related child records. |
| RESTRICT | Rejects a parent deletion or key update when related child records exist. |
| NO ACTION | For InnoDB, behaves like RESTRICT. |
| SET NULL | Sets the related child foreign key column to NULL. |
| SET DEFAULT | Recognized by the MySQL parser but rejected by InnoDB for foreign key referential actions. |
Important: ON DELETE and ON UPDATE can use different actions in the same foreign key definition. For example, deleting a department could be restricted, while changing its department ID could cascade to employees.
ON DELETE specifies what happens to related child records when a referenced row is deleted from the parent table.
CREATE TABLE child_table (
child_id INT PRIMARY KEY,
parent_id INT,
CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(parent_id)
ON DELETE CASCADE
) ENGINE=InnoDB;In this example, ON DELETE CASCADE means that deleting a referenced parent record also deletes child records that reference it.
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INT,
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
) ENGINE=InnoDB;For InnoDB, if no ON DELETE action is specified, the default behavior is effectively NO ACTION, which behaves like RESTRICT. MySQL rejects deletion of a referenced department while matching employee records remain.
ON UPDATE specifies what happens to related child records when a referenced key value in the parent table changes.
CREATE TABLE child_table (
child_id INT PRIMARY KEY,
parent_id INT,
CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(parent_id)
ON UPDATE CASCADE
) ENGINE=InnoDB;With ON UPDATE CASCADE, changing the referenced parent key value automatically updates matching child foreign key values.
Suppose a department has department_id 10 and two employees reference it. If the department ID changes from 10 to 15, ON UPDATE CASCADE changes the employees' department_id values from 10 to 15.
UPDATE departments
SET department_id = 15
WHERE department_id = 10;The update succeeds only if the foreign key definition and other constraints permit it. When ON UPDATE CASCADE is configured, the matching child references are updated automatically.
ON DELETE CASCADE automatically deletes matching child rows when the referenced parent row is deleted. This is useful when child records depend entirely on the existence of a parent record.
CREATE TABLE categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category_id INT,
CONSTRAINT fk_product_category
FOREIGN KEY (category_id)
REFERENCES categories(category_id)
ON DELETE CASCADE
) ENGINE=InnoDB;INSERT INTO categories (category_id, category_name)
VALUES
(1, 'Electronics'),
(2, 'Furniture');
INSERT INTO products (product_id, product_name, category_id)
VALUES
(101, 'Laptop', 1),
(102, 'Keyboard', 1),
(103, 'Office Chair', 2);SELECT * FROM categories;
SELECT * FROM products;DELETE FROM categories
WHERE category_id = 1;Because ON DELETE CASCADE is configured, the Electronics category is deleted along with the Laptop and Keyboard records that reference it.
The Office Chair remains because it belongs to category 2.
ON DELETE CASCADE can delete multiple related records with a single DELETE statement. Use it only when deleting child records is consistent with the intended business rules. For business-critical information such as invoices or financial transactions, restricting deletion or using a soft-delete design may be more appropriate.
ON UPDATE CASCADE automatically updates child foreign key values when the referenced parent key value changes.
CREATE TABLE locations (
location_code INT PRIMARY KEY,
location_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE offices (
office_id INT PRIMARY KEY,
office_name VARCHAR(100) NOT NULL,
location_code INT,
CONSTRAINT fk_office_location
FOREIGN KEY (location_code)
REFERENCES locations(location_code)
ON UPDATE CASCADE
) ENGINE=InnoDB;INSERT INTO locations (location_code, location_name)
VALUES
(100, 'Mumbai'),
(200, 'Pune');
INSERT INTO offices (office_id, office_name, location_code)
VALUES
(1, 'Main Office', 100),
(2, 'Support Office', 100),
(3, 'Regional Office', 200);UPDATE locations
SET location_code = 110
WHERE location_code = 100;Because ON UPDATE CASCADE is enabled, the two offices that referenced location code 100 now reference 110. The Pune office remains associated with location code 200.
In many applications, primary keys are designed to be stable and are rarely changed. ON UPDATE CASCADE is most useful when the referenced key is expected to change and that change should propagate automatically.
ON DELETE RESTRICT prevents a parent row from being deleted while matching child rows exist. It protects related information from becoming inconsistent or being removed unintentionally.
CREATE TABLE publishers (
publisher_id INT PRIMARY KEY,
publisher_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE books (
book_id INT PRIMARY KEY,
book_title VARCHAR(150) NOT NULL,
publisher_id INT,
CONSTRAINT fk_book_publisher
FOREIGN KEY (publisher_id)
REFERENCES publishers(publisher_id)
ON DELETE RESTRICT
) ENGINE=InnoDB;INSERT INTO publishers (publisher_id, publisher_name)
VALUES (1, 'Learning House');
INSERT INTO books (book_id, book_title, publisher_id)
VALUES
(101, 'SQL Fundamentals', 1),
(102, 'Database Design', 1);DELETE FROM publishers
WHERE publisher_id = 1;This statement fails because two books still reference the publisher.
If deleting the books is permitted by the application's rules, remove the dependent rows before deleting the publisher.
DELETE FROM books
WHERE publisher_id = 1;
DELETE FROM publishers
WHERE publisher_id = 1;After the dependent book records are deleted, the publisher can be deleted because no child rows reference it.
ON UPDATE RESTRICT prevents changes to a referenced parent key while matching child records depend on the existing value.
CREATE TABLE suppliers (
supplier_id INT PRIMARY KEY,
supplier_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE supplied_items (
item_id INT PRIMARY KEY,
item_name VARCHAR(100) NOT NULL,
supplier_id INT,
CONSTRAINT fk_item_supplier
FOREIGN KEY (supplier_id)
REFERENCES suppliers(supplier_id)
ON UPDATE RESTRICT
) ENGINE=InnoDB;If an item references supplier_id 10, attempting to change the supplier's ID from 10 to 20 is rejected while the child reference remains.
UPDATE suppliers
SET supplier_id = 20
WHERE supplier_id = 10;To complete a permitted change, consider whether the key should remain unchanged or whether the relationship must be redesigned. Do not remove or disable constraints simply to force an update.
NO ACTION is a referential action name defined by the SQL standard. For MySQL's InnoDB storage engine, NO ACTION behaves like RESTRICT because foreign key checks are immediate rather than deferred until transaction commit.
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
course_id INT,
CONSTRAINT fk_enrollment_course
FOREIGN KEY (course_id)
REFERENCES courses(course_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE=InnoDB;If a course has matching enrollment records, deleting the course is rejected. Updating its referenced course ID is also rejected while those references remain.
| Feature | RESTRICT | NO ACTION |
|---|---|---|
| Purpose | Prevents a parent change when dependent records exist. | Specifies that the parent change must not proceed when dependent records exist. |
| InnoDB behavior | Rejects the operation immediately. | Equivalent to RESTRICT. |
| Default behavior | Functionally equivalent to the default when no action is specified. | The default referential behavior in MySQL. |
ON DELETE SET NULL sets the child foreign key column to NULL when the referenced parent row is deleted. The child row remains in the database, but its relationship to the deleted parent is removed.
This action is suitable when a child record can continue to exist independently of its parent.
CREATE TABLE managers (
manager_id INT PRIMARY KEY,
manager_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100) NOT NULL,
manager_id INT NULL,
CONSTRAINT fk_project_manager
FOREIGN KEY (manager_id)
REFERENCES managers(manager_id)
ON DELETE SET NULL
) ENGINE=InnoDB;The manager_id column is nullable because the referential action must be able to assign NULL.
INSERT INTO managers (manager_id, manager_name)
VALUES
(1, 'Anita Rao'),
(2, 'Vikram Shah');
INSERT INTO projects (project_id, project_name, manager_id)
VALUES
(101, 'Website Redesign', 1),
(102, 'Mobile Application', 1),
(103, 'Database Migration', 2);DELETE FROM managers
WHERE manager_id = 1;The manager record for Anita Rao is deleted. The Website Redesign and Mobile Application project records remain, but their manager_id values become NULL.
SELECT *
FROM projects;The projects can continue to exist without a currently assigned manager.
Do not declare the child foreign key column as NOT NULL if you intend to use SET NULL for the corresponding referential action. MySQL must be able to store NULL when the parent record is deleted or its referenced key is updated.
ON UPDATE SET NULL sets the child foreign key value to NULL when the referenced parent key changes. Unlike ON UPDATE CASCADE, it does not copy the new parent key into the child table.
CREATE TABLE service_regions (
region_id INT PRIMARY KEY,
region_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE service_requests (
request_id INT PRIMARY KEY,
request_description VARCHAR(200) NOT NULL,
region_id INT NULL,
CONSTRAINT fk_request_region
FOREIGN KEY (region_id)
REFERENCES service_regions(region_id)
ON UPDATE SET NULL
) ENGINE=InnoDB;Suppose a service request references region_id 10. If the region's referenced key changes from 10 to 20, the matching request's region_id becomes NULL.
UPDATE service_regions
SET region_id = 20
WHERE region_id = 10;This action is appropriate only when losing the association is acceptable. If the relationship must remain intact after a key change, ON UPDATE CASCADE is generally more suitable.
SET DEFAULT is a referential action name, but it should not be used with InnoDB foreign keys in MySQL. Although the MySQL parser recognizes the syntax, InnoDB rejects table definitions that use ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT.
For example, the following definition is not valid for an InnoDB table:
-- Do not use this definition with InnoDB.
CREATE TABLE child_records (
child_id INT PRIMARY KEY,
parent_id INT DEFAULT 0,
FOREIGN KEY (parent_id)
REFERENCES parent_records(parent_id)
ON DELETE SET DEFAULT
) ENGINE=InnoDB;Declaring a column DEFAULT value does not make ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT supported.
If you need a child record to point to a predefined fallback record, consider using a supported design, such as a valid fallback parent row combined with application logic or an appropriate update workflow. Ensure that any replacement foreign key value actually references an existing parent record.
| Feature | ON DELETE | ON UPDATE |
|---|---|---|
| Triggering operation | A referenced parent row is deleted. | A referenced parent key value is changed. |
| CASCADE | Deletes matching child rows. | Updates matching child foreign key values. |
| RESTRICT | Prevents deleting a referenced parent row. | Prevents changing a referenced key while matching child references exist. |
| NO ACTION | For InnoDB, behaves like RESTRICT. | For InnoDB, behaves like RESTRICT. |
| SET NULL | Sets matching child foreign key values to NULL. | Sets matching child foreign key values to NULL when the referenced key changes. |
A foreign key can define separate actions for deletion and update. This allows the database designer to specify different behaviors for different operations.
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INT,
CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
) ENGINE=InnoDB;In this example:
This design protects department information from accidental deletion while allowing a referenced department identifier to be changed safely.
This practical example demonstrates how referential actions work in a simple order management database.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(150)
) ENGINE=InnoDB;CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NULL,
CONSTRAINT fk_order_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET NULL
ON UPDATE CASCADE
) ENGINE=InnoDB;INSERT INTO customers (customer_id, customer_name, email)
VALUES
(1, 'Rohan Verma', '[email protected]'),
(2, 'Meera Joshi', '[email protected]');INSERT INTO orders (order_id, order_date, customer_id)
VALUES
(1001, '2026-10-01', 1),
(1002, '2026-10-03', 1),
(1003, '2026-10-05', 2);SELECT
orders.order_id,
orders.order_date,
orders.customer_id,
customers.customer_name
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.customer_id;UPDATE customers
SET customer_id = 10
WHERE customer_id = 1;Because ON UPDATE CASCADE is configured, orders 1001 and 1002 now reference customer_id 10.
DELETE FROM customers
WHERE customer_id = 2;Because ON DELETE SET NULL is configured, order 1003 remains in the orders table, but its customer_id becomes NULL.
SELECT * FROM customers;
SELECT * FROM orders;This example demonstrates that ON UPDATE and ON DELETE can be configured independently within a single foreign key constraint.
To change an existing foreign key's referential actions, you generally need to drop the existing foreign key constraint and add it again with the desired actions.
SHOW CREATE TABLE orders;Review the output to find the name of the foreign key constraint. You can also query INFORMATION_SCHEMA.KEY_COLUMN_USAGE to inspect foreign key metadata.
ALTER TABLE orders
DROP FOREIGN KEY fk_order_customer;Use the actual constraint name returned by your table definition. The example assumes the existing constraint is named fk_order_customer.
ALTER TABLE orders
ADD CONSTRAINT fk_order_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET NULL
ON UPDATE CASCADE;Before adding the foreign key again, confirm that existing non-NULL customer_id values in orders reference valid customers. Invalid existing references can prevent the new constraint from being created.
SHOW CREATE TABLE orders;This statement shows the table definition, including its foreign key constraint and the referential actions that are explicitly represented in the output.
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
AND REFERENCED_TABLE_NAME IS NOT NULL;This query lists foreign key relationships in the current database. It is useful for identifying which tables and columns participate in foreign key constraints.
To inspect the referential actions, query INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS:
SELECT
CONSTRAINT_NAME,
TABLE_NAME,
REFERENCED_TABLE_NAME,
UPDATE_RULE,
DELETE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE();| Error or Problem | Possible Cause | Solution |
|---|---|---|
| Cannot delete or update a parent row | Child rows still reference the parent and the action prevents the operation. | Review the foreign key action and handle the dependent rows according to business rules. |
| Cannot add a foreign key constraint | Referenced and referencing columns have incompatible definitions, required indexes are missing, or existing data violates the relationship. | Check column types, indexes, storage engines, and existing foreign key values. |
| SET NULL operation fails | The child foreign key column is declared NOT NULL. | Allow NULL in the child column if SET NULL is appropriate. |
| ON DELETE SET DEFAULT is rejected | InnoDB does not support this referential action. | Choose a supported action or implement a valid fallback workflow. |
| Child records unexpectedly disappear | ON DELETE CASCADE removed dependent records. | Review the schema before deleting parent records and consider whether cascading deletion is appropriate. |
| ON UPDATE CASCADE does not behave as expected | The referenced key may not have changed, the constraint may use another action, or the operation may violate another constraint. | Inspect SHOW CREATE TABLE and verify the update statement. |
An orphaned record is a child row whose foreign key value does not match a parent row. Properly enforced foreign keys prevent these invalid references, but they can appear in data loaded without constraints or after constraints have been disabled or removed.
SELECT orders.*
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.customer_id
WHERE orders.customer_id IS NOT NULL
AND customers.customer_id IS NULL;If the query returns rows, investigate and correct the inconsistent references before adding or re-enabling a foreign key constraint.
SHOW TABLE STATUS
WHERE Name IN ('customers', 'orders');Verify that the tables use a storage engine that supports foreign keys, such as InnoDB.
Create a departments table and an employees table. Configure the employee foreign key so that deleting a department is restricted while employees reference it, but changing the department ID updates the related employee references automatically.
Expected approach: Use ON DELETE RESTRICT and ON UPDATE CASCADE.
Create categories and products tables. Configure the foreign key so that deleting a category also deletes all products associated with that category.
Expected approach: Use ON DELETE CASCADE.
Create managers and projects tables. When a manager is deleted, the project records should remain, but their manager_id should become NULL.
Expected approach: Use ON DELETE SET NULL and make manager_id nullable.
Create customers and orders tables. Prevent deleting a customer while orders reference the customer.
Expected approach: Use ON DELETE RESTRICT or rely on the default restrictive behavior for InnoDB.
Create a foreign key using the default referential behavior. Then drop it and add it again using ON DELETE SET NULL and ON UPDATE CASCADE. Verify the new definition with SHOW CREATE TABLE.
Project objective: Design a simple library database with authors and books, and apply suitable referential actions.
CREATE TABLE authors (
author_id INT PRIMARY KEY,
author_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;CREATE TABLE library_books (
book_id INT PRIMARY KEY,
book_title VARCHAR(150) NOT NULL,
author_id INT,
CONSTRAINT fk_book_author
FOREIGN KEY (author_id)
REFERENCES authors(author_id)
ON DELETE SET NULL
ON UPDATE CASCADE
) ENGINE=InnoDB;INSERT INTO authors (author_id, author_name)
VALUES
(1, 'Author One'),
(2, 'Author Two');
INSERT INTO library_books (book_id, book_title, author_id)
VALUES
(101, 'Introduction to SQL', 1),
(102, 'Database Concepts', 1),
(103, 'Learning MySQL', 2);UPDATE authors
SET author_id = 10
WHERE author_id = 1;The two books that reference author 1 should now reference author 10.
DELETE FROM authors
WHERE author_id = 2;The book titled Learning MySQL remains in the library_books table, but its author_id becomes NULL.
SELECT * FROM authors;
SELECT * FROM library_books;Answer: ON DELETE defines what happens to related child records when a referenced parent row is deleted.
Answer: ON UPDATE defines what happens to child foreign key values when a referenced parent key value changes.
Answer: It automatically deletes child rows that reference a parent row when that parent row is deleted.
Answer: It automatically updates matching child foreign key values when the referenced parent key changes.
Answer: CASCADE propagates the parent operation to related child records, while RESTRICT prevents the parent operation if matching child references exist.
Answer: SET NULL changes matching child foreign key values to NULL when the relevant parent deletion or referenced key update occurs.
Answer: No. The child foreign key column must permit NULL so that MySQL can apply the referential action.
Answer: They behave equivalently in InnoDB. Both reject the parent operation when matching child references exist.
Answer: No. Although the parser recognizes the clause, InnoDB rejects foreign key definitions that use SET DEFAULT as a referential action.
Answer: In InnoDB, the default behavior is NO ACTION, which behaves like RESTRICT.
Answer: Use SHOW CREATE TABLE to inspect the table definition and its foreign key clauses.
Answer: Identify the existing constraint, drop it using ALTER TABLE ... DROP FOREIGN KEY, and recreate it with the required ON DELETE and ON UPDATE clauses.
ON DELETE and ON UPDATE referential actions are important features of MySQL foreign key constraints. They help maintain consistent relationships between parent and child tables when referenced records are deleted or their key values change.
CASCADE is useful when related child records should automatically follow a parent operation. RESTRICT prevents changes that would conflict with existing relationships. SET NULL preserves child records while removing their association with the parent, provided the foreign key column allows NULL. For InnoDB, NO ACTION behaves like RESTRICT, while SET DEFAULT is not supported as a referential action.
Choosing the correct action depends on the database's business rules, the importance of the related records, and whether child records should be deleted, retained, or disconnected from their parent. Practice these actions with sample tables and verify the results using SELECT and SHOW CREATE TABLE.
Continue Learning: Explore the MySQL Training Course at JustAcademy, visit the JustAcademy website, or register for a course demo.
